package at.ac.univie.bi.transformation.service.output;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDate;
import java.time.ZoneId;

import at.ac.univie.bi.transformation.model.output.AbstractOutputEntity;
import at.ac.univie.bi.transformation.persistence.DBHandler;

public class ProcessCaseService extends AbstractOutputEntityService{
	public int createBlankProcessCase(){
		String sqlstatement = "INSERT INTO Processcase (externalidentifier) VALUES ('')";
		
		int pcid = executeInsertStatement(sqlstatement);
		return pcid;
	}
	
	public int createProcessCase(int oldid){
		String sqlstatement = "INSERT INTO Processcase (externalidentifier) VALUES ('"+ oldid +"')";
		
		int pcid = executeInsertStatement(sqlstatement);
		return pcid;
	}
	
	public void listProcessCases(){
	  String sqlstatement = "SELECT * FROM Processcase";
	  try
      {
		  Statement stmt = DBHandler.getIntegrationDB().createStatement();
          ResultSet results = stmt.executeQuery(sqlstatement);
          ResultSetMetaData rsmd = results.getMetaData();
          int numberCols = rsmd.getColumnCount();
          for (int i=1; i<=numberCols; i++)
          {
              //print Column Names
              System.out.print(rsmd.getColumnLabel(i)+"\t\t");  
          }

          System.out.println("\n-------------------------------------------------");

          while(results.next())
          {
              int id = results.getInt(1);
              String extid = results.getString(2);
              System.out.println(id + "\t\t" + extid);
          }
          
          results.close();
          stmt.close();
      }
      catch (SQLException sqlExcept)
      {
          sqlExcept.printStackTrace();
      }

	}
	
	public int getParameterId(String name){
		 String sqlstatement = "SELECT * FROM Parameter WHERE name = '"+name+"' ";
		 int id = 0;
		  try
	      {
			  Statement stmt = DBHandler.getIntegrationDB().createStatement();
	          ResultSet results = stmt.executeQuery(sqlstatement);
	       

	          while(results.next())
	          {
	              id = results.getInt(1);
	          }
	          results.close();
	          stmt.close();
	      }
	      catch (SQLException sqlExcept)
	      {
	          sqlExcept.printStackTrace();
	      }
		  return id;
	}
	
	public int executeInsertStatement(String sqlstatement){
	  int newid = 0;
	  
	  try
      {
		  String generatedColumns[] = {"ID"};
          PreparedStatement pstmt =  DBHandler.getIntegrationDB().prepareStatement(sqlstatement, generatedColumns);
          pstmt.executeUpdate();
          
          ResultSet rs = pstmt.getGeneratedKeys();
          while (rs.next()){
        	    newid = rs.getInt(1);
          }	
      } 
	  catch (Exception e) {	
			System.err.println("Error: " + e.getMessage());
	  } 
	  
	  return newid;
  	}

	@Override
	public int save(AbstractOutputEntity element) {
		// TODO Auto-generated method stub
		return 0;
	}
}
